package com.bj58.data.emailreports.service.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import com.bj58.data.emailreports.dao.CommonDao;
import com.bj58.data.emailreports.dao.SrcDbPool.DBType;
import com.bj58.data.emailreports.model.SearchKWReportVo;
import com.bj58.data.emailreports.model.UserSearchCount;
import com.bj58.data.emailreports.model.UserSearchInfoAnalysis;
import com.bj58.data.emailreports.service.KWDataService;
import com.bj58.data.emailreports.utils.FileUtil;
import com.bj58.groupbuy.util.PathUtil;

public class KWDataServiceImpl implements KWDataService {
	private CommonDao comDao = new CommonDao();
	private FileUtil fileUtil = new FileUtil();
	
	@Override
	public List<SearchKWReportVo> searchKeyWordData(String beginDate,
			String endDate,int productType,int wordType) throws Exception {
		String beginMonth = beginDate.substring(0,beginDate.lastIndexOf("-")).replace("-", "");
		String endMonth = endDate.substring(0,endDate.lastIndexOf("-")).replace("-", "");
		
		List<SearchKWReportVo> rsList = new ArrayList<SearchKWReportVo>();
		boolean sameMonth = true;
		//跨月处理   将跨月一周数据全部取出放入临时表进行查询    user_search_info_analysis_temp    user_search_count_temp
		if(!beginMonth.equals(endMonth)){
			sameMonth = false;
			
			if(wordType == 1){ //查询热门词时才处理临时数据  （即第一次时读取+存一次）
				//comDao.executeSql("DROP table user_search_info_analysis_temp", DBType.infobright_58.getValue());
				//comDao.executeSql("DROP table user_search_count_temp", DBType.infobright_58.getValue());
				comDao.executeSql("DROP table user_search_info_analysis_temp", DBType.db_94_data_app.getValue());
				comDao.executeSql("DROP table user_search_count_temp", DBType.db_94_data_app.getValue());
				
				StringBuffer sql = new StringBuffer();
				sql.append("CREATE TABLE `user_search_info_analysis_temp` (");
				sql.append(" `stat_date` date DEFAULT NULL COMMENT '日期',");
				sql.append(" `word` varchar(256) DEFAULT NULL COMMENT '搜索词',");
				sql.append(" `word_type` varchar(30) DEFAULT NULL COMMENT '搜索词类型（包括：一般词，类目词，区域词，区域类别组合词）',");
				sql.append(" `page_num` int(10) DEFAULT NULL COMMENT '页码编号',");
				sql.append(" `city1` varchar(30) DEFAULT NULL COMMENT '一级城市',");
				sql.append(" `city2` varchar(30) DEFAULT NULL COMMENT '二级城市',");
				sql.append(" `city3` varchar(30) DEFAULT NULL COMMENT '三级城市',");
				sql.append(" `cate1` varchar(30) DEFAULT NULL COMMENT '一级类别',");
				sql.append(" `cate2` varchar(30) DEFAULT NULL COMMENT '二级类别',");
				sql.append(" `cate3` varchar(30) DEFAULT NULL COMMENT '三级类别',");
				sql.append(" `post_type` varchar(30) DEFAULT NULL COMMENT '操作类型',");
				sql.append(" `user_type` varchar(30) DEFAULT NULL COMMENT '用户类型',");
				sql.append(" `show_count` double DEFAULT NULL COMMENT '展现次数',");
				sql.append(" `click_count` int(16) DEFAULT NULL COMMENT '点击次数'");
				sql.append("	) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;");
				//comDao.executeSql(sql.toString(), DBType.infobright_58.getValue());
				comDao.executeSql(sql.toString(), DBType.db_94_data_app.getValue());
				
				sql.delete(0, sql.length()+1);
				sql.append("CREATE TABLE `user_search_count_temp` (");
				sql.append("`stat_date` date DEFAULT NULL COMMENT '日期',");
				sql.append("`word` varchar(256) DEFAULT NULL COMMENT '搜索词',");
				sql.append("`word_type` varchar(30) DEFAULT NULL COMMENT '搜索词类型（包括：一般词，类目词，区域词，区域类别组合词）',");
				sql.append("`page_num` int(10) DEFAULT NULL COMMENT '页码编号', ");
				sql.append(" `city1` varchar(30) DEFAULT NULL COMMENT '一级城市',");
				sql.append("`city2` varchar(30) DEFAULT NULL COMMENT '二级城市',");
				sql.append(" `city3` varchar(30) DEFAULT NULL COMMENT '三级城市',");
				sql.append("  `cate1` varchar(30) DEFAULT NULL COMMENT '一级类别',");
				sql.append(" `cate2` varchar(30) DEFAULT NULL COMMENT '二级类别',");
				sql.append("  `cate3` varchar(30) DEFAULT NULL COMMENT '三级类别',");
				sql.append("  `source` int(2) DEFAULT NULL COMMENT 'source(0首页，1列表页，2详情页，3my.58.com 4外部) ',");
				sql.append(" `jump` int(2) DEFAULT NULL COMMENT 'jump(0全站搜，1类目)',");
				sql.append(" `search_count` int(16) DEFAULT NULL COMMENT '搜索次数',");
				sql.append("`cate_list_name` varchar(50) DEFAULT NULL COMMENT '类别列表名称'");
				sql.append(") ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;");
				//comDao.executeSql(sql.toString(), DBType.infobright_58.getValue());
				comDao.executeSql(sql.toString(), DBType.db_94_data_app.getValue());
				
				//查询跨月的数据并LOAD入临时表
				sql.delete(0, sql.length()+1);
				sql.append("select * ");
				if(productType == 1){
					sql.append(" FROM hy_user_search_info_analysis_");
				}else if(productType == 2){
					sql.append(" FROM zp_user_search_info_analysis_");
				}else if(productType == 3){
					sql.append(" FROM fc_user_search_info_analysis_");
				}else if(productType == 4){
					sql.append(" FROM es_user_search_info_analysis_");
				}else if(productType == 5){
					sql.append(" FROM qc_user_search_info_analysis_");
				}else if(productType == 6){
					sql.append(" FROM other_user_search_info_analysis_");
				}
				
				
				String sql1 = sql.toString()+beginMonth+" where stat_date >='"+beginDate+"' ";
				String sql2 = sql.toString()+endMonth+" where stat_date <='"+endDate+"' ";
				int searchCount = 0 ;
				int limitCount = 1000000;
				while (true) {
					String limit = " limit "+limitCount*searchCount +","+limitCount;
					System.out.println(limit);
					//List<UserSearchInfoAnalysis> usiaList1 = comDao.getAllColsByVo(sql1+limit, UserSearchInfoAnalysis.class, DBType.infobright_58.getValue());
					//List<UserSearchInfoAnalysis> usiaList2 = comDao.getAllColsByVo(sql2+limit, UserSearchInfoAnalysis.class, DBType.infobright_58.getValue());
					List<UserSearchInfoAnalysis> usiaList1 = comDao.getAllColsByVo(sql1+limit, UserSearchInfoAnalysis.class, DBType.db_94_data_app.getValue());
					List<UserSearchInfoAnalysis> usiaList2 = comDao.getAllColsByVo(sql2+limit, UserSearchInfoAnalysis.class, DBType.db_94_data_app.getValue());
					
					
					System.out.println("usiaList1 size is"+usiaList1.size());
					System.out.println("usiaList2 size is"+usiaList2.size());
					if((null == usiaList1 || usiaList1.isEmpty()) && (null == usiaList2 || usiaList2.isEmpty()) ){
						break;
					}
					usiaList1.addAll(usiaList2);
					String filePath = PathUtil.getWFNamespaceFolder()+"/dbrecord/usia_temp_"+beginDate+".txt";
					filePath = fileUtil.writeFileByVo(filePath, usiaList1, UserSearchInfoAnalysis.class, "^&");
					System.out.println("begin load");
					//comDao.loadData(filePath, "user_search_info_analysis_temp", "^&", DBType.infobright_58.getValue());
					comDao.loadData(filePath, "user_search_info_analysis_temp", "^&", DBType.db_94_data_app.getValue());
					
					System.out.println("end load");
					usiaList1 = null;	
					usiaList2 = null;
					searchCount ++;
				}
				
				
				sql.delete(0, sql.length()+1);
				sql.append("select * ");
				if(productType == 1){
					sql.append(" FROM hy_user_search_count_");
				}else if(productType == 2){
					sql.append(" FROM zp_user_search_count_");
				}else if(productType == 3){
					sql.append(" FROM fc_user_search_count_");
				}else if(productType == 4){
					sql.append(" FROM es_user_search_count_");
				}else if(productType == 5){
					sql.append(" FROM qc_user_search_count_");
				}else if(productType == 6){
					sql.append(" FROM other_user_search_count_");
				}
				
				String sql3 = sql.toString()+beginMonth+" where stat_date >='"+beginDate+"' ";
				String sql4 = sql.toString()+endMonth+" where stat_date <='"+endDate+"'  ";
				searchCount = 0 ;
				while (true) {
					String limit = " limit "+limitCount*searchCount +","+limitCount;
					System.out.println(limit);
					//List<UserSearchCount> uscList1 = comDao.getAllColsByVo(sql3 + limit, UserSearchCount.class, DBType.infobright_58.getValue());
					//List<UserSearchCount> uscList2 = comDao.getAllColsByVo(sql4 + limit, UserSearchCount.class, DBType.infobright_58.getValue());
					List<UserSearchCount> uscList1 = comDao.getAllColsByVo(sql3 + limit, UserSearchCount.class, DBType.db_94_data_app.getValue());
					List<UserSearchCount> uscList2 = comDao.getAllColsByVo(sql4 + limit, UserSearchCount.class, DBType.db_94_data_app.getValue());
					
					System.out.println("uscList1 size is"+uscList1.size());
					System.out.println("uscList2 size is"+uscList2.size());
					if((null == uscList1 || uscList1.isEmpty()) && (null == uscList2 || uscList2.isEmpty()) ){
						break;
					}
					uscList1.addAll(uscList2);
					String filePath2 = PathUtil.getWFNamespaceFolder()+"/dbrecord/usc_temp_"+beginDate+".txt";
					filePath2 = fileUtil.writeFileByVo(filePath2, uscList1, UserSearchCount.class, "^&");
					System.out.println("begin load");
					//comDao.loadData(filePath2, "user_search_count_temp", "^&", DBType.infobright_58.getValue());
					comDao.loadData(filePath2, "user_search_count_temp", "^&", DBType.db_94_data_app.getValue());
					
					System.out.println("end load");
					uscList1 = null;
					uscList2 = null;
					searchCount ++;
				}
			}
		}

		StringBuffer sql = new StringBuffer();
		List<String> cols = new ArrayList<String>();
		cols.add("word");
		
		if(wordType == 1){ //热门词
			sql.append(" SELECT h.word, ccnt, maxcnt, cate_list_name, SUM(show_count)/maxcnt, ");
			sql.append(" SUM(CASE WHEN post_type IN ('房产竞价','精准推广', '高级置顶', '置顶', '金铺商品', '精品信息') THEN show_count END)/SUM(show_count), ");
			sql.append(" SUM(CASE WHEN post_type IN ('房产竞价','精准推广', '高级置顶', '置顶', '金铺商品', '精品信息') THEN click_count END)/SUM(click_count), ");
			sql.append(" SUM(click_count)/SUM(show_count),");
			sql.append(" SUM(CASE WHEN post_type IN ('房产竞价','精准推广') THEN show_count ELSE 0 END)/SUM(show_count),  ");
			sql.append(" SUM(CASE WHEN post_type IN ('高级置顶','置顶') THEN show_count ELSE 0 END)/SUM(show_count),  ");
			sql.append(" SUM(CASE WHEN post_type IN ('精品信息','金铺商品') THEN show_count ELSE 0 END)/SUM(show_count)");
			if(!sameMonth){
				sql.append(" FROM user_search_info_analysis_temp");
			}else{
				//productType 1:黄页  2:招聘  3:房产    4:二手市场  5:二手车 6.其他
				if(productType == 1){
					sql.append(" FROM hy_user_search_info_analysis_"+beginMonth);
				}else if(productType == 2){
					sql.append(" FROM zp_user_search_info_analysis_"+beginMonth);
				}else if(productType == 3){
					sql.append(" FROM fc_user_search_info_analysis_"+beginMonth);
				}else if(productType == 4){
					sql.append(" FROM es_user_search_info_analysis_"+beginMonth);
				}else if(productType == 5){
					sql.append(" FROM qc_user_search_info_analysis_"+beginMonth);
				}else if(productType == 6){
					sql.append(" FROM other_user_search_info_analysis_"+beginMonth);
				}
			}
			sql.append(" h, (SELECT a.word, a.cate_list_name, COUNT(DISTINCT a.city1) ccnt, maxcnt, cate2, cate3 ");
			if(!sameMonth){
				sql.append(" FROM user_search_count_temp");
			}else{
				//productType 1:黄页  2:招聘  3:房产    4:二手市场  5:二手车 6.其他
				if(productType == 1){
					sql.append(" FROM hy_user_search_count_"+beginMonth);
				}else if(productType == 2){
					sql.append(" FROM zp_user_search_count_"+beginMonth);
				}else if(productType == 3){
					sql.append(" FROM fc_user_search_count_"+beginMonth);
				}else if(productType == 4){
					sql.append(" FROM es_user_search_count_"+beginMonth);
				}else if(productType == 5){
					sql.append(" FROM qc_user_search_count_"+beginMonth);
				}else if(productType == 6){
					sql.append(" FROM other_user_search_count_"+beginMonth);
				}
			}
					
			sql.append(" a , (SELECT word, MAX(scntt) maxcnt");
			sql.append(" FROM (SELECT word, SUM(search_count) AS scntt ,cate_list_name");
			
			if(!sameMonth){
				sql.append(" FROM user_search_count_temp");
			}else{
				//productType 1:黄页  2:招聘  3:房产    4:二手市场  5:二手车 6.其他
				if(productType == 1){
					sql.append(" FROM hy_user_search_count_"+beginMonth);
				}else if(productType == 2){
					sql.append(" FROM zp_user_search_count_"+beginMonth);
				}else if(productType == 3){
					sql.append(" FROM fc_user_search_count_"+beginMonth);
				}else if(productType == 4){
					sql.append(" FROM es_user_search_count_"+beginMonth);
				}else if(productType == 5){
					sql.append(" FROM qc_user_search_count_"+beginMonth);
				}else if(productType == 6){
					sql.append(" FROM other_user_search_count_"+beginMonth);
				}
			}
			sql.append(" WHERE  source != 4  and stat_date >='"+beginDate+"' and stat_date <='"+endDate+"' ");
			sql.append(" GROUP BY word, cate_list_name");
			sql.append(" HAVING SUM(search_count) > 300 AND  COUNT(DISTINCT city1) > 100) c ");
			sql.append(" GROUP BY word) b ");
			sql.append(" WHERE source != 4 AND a.word = b.word  and  stat_date >='"+beginDate+"' and stat_date <='"+endDate+"' ");
			sql.append(" GROUP BY a.word, a.cate_list_name");
			sql.append(" HAVING SUM(search_count) = maxcnt) t");
			sql.append(" WHERE h.word = t.word    and  stat_date >='"+beginDate+"' and stat_date <='"+endDate+"' ");
			if(productType != 6){ //其他类别业务线 去掉CATE2条件
				sql.append(" AND h.cate2 = t.cate2 ");
			}
			sql.append(" GROUP BY h.word");
			sql.append(" HAVING SUM(show_count) > 0 ");
			sql.append(" ORDER BY maxcnt DESC ");
			sql.append(" LIMIT 50 ");
			
			cols.add("cityCount");
		}else if(wordType == 2 || wordType == 3){// 高频、中低频词
			sql.append(" SELECT h.word,h.city1, maxcnt, cate_list_name, SUM(show_count)/maxcnt, ");
			sql.append(" SUM(CASE WHEN post_type IN ('房产竞价','精准推广', '高级置顶', '置顶', '金铺商品', '精品信息') THEN show_count ELSE 0 END)/SUM(show_count),  ");
			sql.append(" SUM(CASE WHEN post_type IN ('房产竞价','精准推广', '高级置顶', '置顶', '金铺商品', '精品信息') THEN click_count ELSE 0 END)/SUM(click_count), ");
			sql.append(" SUM(click_count)/SUM(show_count),");
			sql.append(" SUM(CASE WHEN post_type IN ('房产竞价','精准推广') THEN show_count ELSE 0 END)/SUM(show_count), ");
			sql.append(" SUM(CASE WHEN post_type IN ('高级置顶','置顶') THEN show_count ELSE 0 END)/SUM(show_count), ");
			sql.append(" SUM(CASE WHEN post_type IN ('精品信息','金铺商品') THEN show_count ELSE 0 END)/SUM(show_count)");
			if(!sameMonth){
				sql.append(" FROM user_search_info_analysis_temp");
			}else{
				//productType 1:黄页  2:招聘  3:房产    4:二手市场  5:二手车  6.其他
				if(productType == 1){
					sql.append(" FROM hy_user_search_info_analysis_"+beginMonth);
				}else if(productType == 2){
					sql.append(" FROM zp_user_search_info_analysis_"+beginMonth);
				}else if(productType == 3){
					sql.append(" FROM fc_user_search_info_analysis_"+beginMonth);
				}else if(productType == 4){
					sql.append(" FROM es_user_search_info_analysis_"+beginMonth);
				}else if(productType == 5){
					sql.append(" FROM qc_user_search_info_analysis_"+beginMonth);
				}else if(productType == 6){
					sql.append(" FROM other_user_search_info_analysis_"+beginMonth);
				}
			}
			
			sql.append(" h, (SELECT a.word, a.cate_list_name, COUNT(DISTINCT a.city1) ccnt, maxcnt, cate2, cate3 ");
			if(!sameMonth){
				sql.append(" FROM user_search_count_temp");
			}else{
				//productType 1:黄页  2:招聘  3:房产    4:二手市场  5:二手车 6.其他
				if(productType == 1){
					sql.append(" FROM hy_user_search_count_"+beginMonth);
				}else if(productType == 2){
					sql.append(" FROM zp_user_search_count_"+beginMonth);
				}else if(productType == 3){
					sql.append(" FROM fc_user_search_count_"+beginMonth);
				}else if(productType == 4){
					sql.append(" FROM es_user_search_count_"+beginMonth);
				}else if(productType == 5){
					sql.append(" FROM qc_user_search_count_"+beginMonth);
				}else if(productType == 6){
					sql.append(" FROM other_user_search_count_"+beginMonth);
				}
			}
			
			sql.append(" a , (SELECT word, MAX(scntt) maxcnt ");
			sql.append(" FROM (SELECT word, SUM(search_count) AS scntt,cate_list_name ");

			if(!sameMonth){
				sql.append(" FROM user_search_count_temp");
			}else{
				//productType 1:黄页  2:招聘  3:房产    4:二手市场  5:二手车 6.其他
				if(productType == 1){
					sql.append(" FROM hy_user_search_count_"+beginMonth);
				}else if(productType == 2){
					sql.append(" FROM zp_user_search_count_"+beginMonth);
				}else if(productType == 3){
					sql.append(" FROM fc_user_search_count_"+beginMonth);
				}else if(productType == 4){
					sql.append(" FROM es_user_search_count_"+beginMonth);
				}else if(productType == 5){
					sql.append(" FROM qc_user_search_count_"+beginMonth);
				}else if(productType == 6){
					sql.append(" FROM other_user_search_count_"+beginMonth);
				}
			}
			sql.append(" WHERE  source != 4 and stat_date >='"+beginDate+"' and stat_date <='"+endDate+"' ");
			sql.append(" GROUP BY word, cate_list_name  ");
			
			if(wordType == 2){
				sql.append(" HAVING SUM(search_count) > 200 AND  COUNT(DISTINCT city1) < 100 ");
			}
			
			sql.append(" )  c  GROUP BY word) b ");
			sql.append(" WHERE  source != 4 AND a.word = b.word  and stat_date >='"+beginDate+"' and stat_date <='"+endDate+"' ");
			sql.append(" GROUP BY a.word, a.cate_list_name");
			sql.append(" HAVING SUM(search_count) = maxcnt) t");
			sql.append(" WHERE h.word = t.word   and stat_date >='"+beginDate+"' and stat_date <='"+endDate+"' ");
			if(productType != 6){ //其他类别业务线 去掉CATE2条件
				sql.append(" AND h.cate2 = t.cate2 ");
			}
			sql.append(" GROUP BY h.word ");
			sql.append(" HAVING SUM(show_count) > 0 ");
			sql.append(" ORDER BY maxcnt DESC ");
		
			if(wordType == 2){ //高频词  取TOP 250
				sql.append(" limit 0,250 ");
			}else if(wordType == 3){//中低频词 取TOP 250之后
				sql.append(" limit 250,99999 ");
			}
			cols.add("city");
		}else if(wordType == 4){
			sql.append(" SELECT h.word, h.city1, maxcnt, cate_list_name, SUM(show_count)/maxcnt,");
			sql.append(" SUM(CASE WHEN post_type IN ('房产竞价','精准推广', '高级置顶', '置顶', '金铺商品', '精品信息') THEN show_count ELSE 0 END)/SUM(show_count), ");
			sql.append(" SUM(CASE WHEN post_type IN ('房产竞价','精准推广', '高级置顶', '置顶', '金铺商品', '精品信息') THEN click_count ELSE 0 END)/SUM(click_count), ");
			sql.append(" SUM(click_count)/SUM(show_count),");
			sql.append(" SUM(CASE WHEN post_type IN ('房产竞价','精准推广') THEN show_count ELSE 0 END)/SUM(show_count),  ");
			sql.append(" SUM(CASE WHEN post_type IN ('高级置顶','置顶') THEN show_count ELSE 0 END)/SUM(show_count), ");
			sql.append(" SUM(CASE WHEN post_type IN ('精品信息','金铺商品') THEN show_count ELSE 0 END)/SUM(show_count) ");
			if(!sameMonth){
				sql.append(" FROM user_search_info_analysis_temp");
			}else{
				//productType 1:黄页  2:招聘  3:房产    4:二手市场  5:二手车 6.其他
				if(productType == 1){
					sql.append(" FROM hy_user_search_info_analysis_"+beginMonth);
				}else if(productType == 2){
					sql.append(" FROM zp_user_search_info_analysis_"+beginMonth);
				}else if(productType == 3){
					sql.append(" FROM fc_user_search_info_analysis_"+beginMonth);
				}else if(productType == 4){
					sql.append(" FROM es_user_search_info_analysis_"+beginMonth);
				}else if(productType == 5){
					sql.append(" FROM qc_user_search_info_analysis_"+beginMonth);
				}else if(productType == 6){
					sql.append(" FROM other_user_search_info_analysis_"+beginMonth);
				}
			}
			
			sql.append(" h, (SELECT a.word, a.cate_list_name, COUNT(DISTINCT a.city1) ccnt, maxcnt, cate2, cate3 ");
			
			if(!sameMonth){
				sql.append(" FROM user_search_count_temp");
			}else{
				//productType 1:黄页  2:招聘  3:房产    4:二手市场  5:二手车 6.其他
				if(productType == 1){
					sql.append(" FROM hy_user_search_count_"+beginMonth);
				}else if(productType == 2){
					sql.append(" FROM zp_user_search_count_"+beginMonth);
				}else if(productType == 3){
					sql.append(" FROM fc_user_search_count_"+beginMonth);
				}else if(productType == 4){
					sql.append(" FROM es_user_search_count_"+beginMonth);
				}else if(productType == 5){
					sql.append(" FROM qc_user_search_count_"+beginMonth);
				}else if(productType == 6){
					sql.append(" FROM other_user_search_count_"+beginMonth);
				}
			}
			sql.append("  a , (SELECT word, MAX(scntt) maxcnt ");
			
			sql.append(" FROM (SELECT word, SUM(search_count) AS scntt ");
			if(!sameMonth){
				sql.append(" FROM user_search_count_temp");
			}else{
				//productType 1:黄页  2:招聘  3:房产    4:二手市场  5:二手车 6.其他
				if(productType == 1){
					sql.append(" FROM hy_user_search_count_"+beginMonth);
				}else if(productType == 2){
					sql.append(" FROM zp_user_search_count_"+beginMonth);
				}else if(productType == 3){
					sql.append(" FROM fc_user_search_count_"+beginMonth);
				}else if(productType == 4){
					sql.append(" FROM es_user_search_count_"+beginMonth);
				}else if(productType == 5){
					sql.append(" FROM qc_user_search_count_"+beginMonth);
				}else if(productType == 6){
					sql.append(" FROM other_user_search_count_"+beginMonth);
				}
			}
			
			
			sql.append(" WHERE source = 4  and stat_date >='"+beginDate+"' and stat_date <='"+endDate+"' ");
			sql.append(" GROUP BY word, cate_list_name) c  GROUP BY c.word ) b  ");
			sql.append(" WHERE  source = 4 AND a.word = b.word and stat_date >='"+beginDate+"' and stat_date <='"+endDate+"' ");
			sql.append(" GROUP BY a.word, a.cate_list_name");
			sql.append(" HAVING SUM(a.search_count) = maxcnt) t");
			sql.append(" WHERE h.word = t.word    and stat_date >='"+beginDate+"' and stat_date <='"+endDate+"' ");
			if(productType != 6){ //其他类别业务线 去掉CATE2条件
				sql.append(" AND h.cate2 = t.cate2 ");
			}
			sql.append(" GROUP BY h.word");
			sql.append(" HAVING SUM(show_count) > 0 ");
			sql.append(" ORDER BY maxcnt DESC ");
			sql.append(" LIMIT 0,50 ");
			
			
			cols.add("city");
		}
		
		cols.add("searchCount");cols.add("cate");cols.add("avgShowCount");cols.add("payShowRate");cols.add("payClickRate");
		cols.add("tranRate");cols.add("preShowRate");cols.add("topShowRate");cols.add("vipShowRate");
		System.out.println(sql.toString());
		//rsList = comDao.getCommonStatList(sql.toString(), cols, SearchKWReportVo.class, DBType.infobright_58.getValue());
		rsList = comDao.getCommonStatList(sql.toString(), cols, SearchKWReportVo.class, DBType.db_94_data_app.getValue());
		
		if((wordType ==2  || wordType == 3) && rsList.size() > 50){ //高频词,中低频词  在结果集中随机取 50个
			List<SearchKWReportVo> finalList = new ArrayList<SearchKWReportVo>();
			List<Integer>  ranList = new ArrayList<Integer>();
			while (true) {
				int ran = new Random().nextInt(rsList.size());
				if(!ranList.contains(ran)){
					finalList.add(rsList.get(ran));
					ranList.add(ran);
				}
				if(finalList.size() == 50){
					break;
				}
			}
			rsList = finalList;
		}
	
		System.out.println("rsList size is "+ rsList.size());
		return rsList;
	}

}
